[Oracle Debug] 實現用regexp_substr分解資料成多個欄位

* 目標

給定資料如下：將此字串分割成三個欄位

SELECT '1^^2' AS temp_value
FROM dual

* 使用regexp_substr (方法1)

WITH temp AS
(
    SELECT '1^2^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

* 使用regexp_substr (方法2)

當遇到有「空值」的時候，方法1會造成資料和欄位錯置

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3) AS cc
FROM temp

* 輸出結果

* 使用regexp_substr (方法2 改良)

WITH temp AS
(
    SELECT '1^^3' AS temp_value
    FROM dual
)
SELECT regexp_substr(temp_value, '(.*?)(\^|$)', 1, 1, null, 1) AS aa
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 2, null, 1) AS bb
    , regexp_substr(temp_value, '(.*?)(\^|$)', 1, 3, null, 1) AS cc
FROM temp

* 輸出結果